using System;
using System.Data;
using System.Data.SqlClient;


namespace BusObjectCS {

   public class IBAProducts {

      private string m_DSN;

      public  IBAProducts () {
      	m_DSN="";
      }

      public  IBAProducts (string DSN) {
         m_DSN = DSN;
      }

      public string DatabaseConnection {
         set { m_DSN = value; }
         get { return m_DSN; }
      }

      public DataSet GetProducts (string productType) {
          if (m_DSN == "") 
          {
              throw new ArgumentNullException("DatabaseConnection", "No value for the database connection string");
          }

          SqlConnection myConnection = new SqlConnection(m_DSN);
          SqlDataAdapter sqlAdapter1 = new SqlDataAdapter("SELECT * FROM Products WHERE ProductType='"+productType+"'", myConnection);

          DataSet products = new DataSet();
          sqlAdapter1.Fill(products, "products");

          return products;
      }

      public DataSet GetProductTypes () {

          if (m_DSN == "") 
          {
              throw new ArgumentNullException("DatabaseConnection", "No value for the database connection string");
          }

         SqlConnection dbConnection = new SqlConnection(m_DSN);
         dbConnection.Open();

         SqlDataAdapter sqlAdapter1 = new SqlDataAdapter("SELECT DISTINCT ProductType FROM Products", dbConnection);

         DataSet types = new DataSet();
         sqlAdapter1.Fill(types, "ProdTypes");
			
         return types;
      }

      public Double AveragePrice (string productType) {
          if (m_DSN == "") 
          {
              throw new ArgumentNullException("DatabaseConnection", "No value for the database connection string");
          }
         SqlConnection dbConnection = new SqlConnection(m_DSN);
         dbConnection.Open();

         SqlDataAdapter sqlAdapter1 = new SqlDataAdapter(
         "SELECT AVG(UnitPrice) AS AveragePrice FROM Products WHERE ProductType='"+productType+"'", dbConnection);

         DataSet AvgPrice = new DataSet();
         sqlAdapter1.Fill(AvgPrice, "AveragePrice");


         DataTable priceTable; 
         priceTable = AvgPrice.Tables["AveragePrice"];
         if (priceTable.Rows.Count > 0)
         {
            return (Double)priceTable.Rows[0]["AveragePrice"];
            }
         else
            return 0;
      }
   }
}
